package com.threeti.mecool.core.application;

import com.threeti.mecool.core.domain.model.dto.ChannelSyncDto;
import com.threeti.mecool.core.domain.model.plan.PlanJobResult;
import com.threeti.mecool.core.domain.model.plan.pojo.Job;
import com.threeti.mecool.core.domain.model.plan.pojo.PlanJob;
import com.threeti.mecool.core.infrastructure.persistence.basicdata.biz.ChannelSyncRepository;
import com.threeti.mecool.core.infrastructure.persistence.plan.PlanProjectRepository;
import org.hibernate.dialect.Oracle10gDialect;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

@Service
public class SearchService {
    private static Logger log = LoggerFactory.getLogger(SearchService.class);
    private EntityManager entityManager;
    private PlanProjectRepository planProjectRepository;
    private ChannelSyncRepository channelSyncRepository;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    //<editor-fold desc="setter">
    @PersistenceContext
    public void setEntityManager(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    @Autowired
    public void setPlanProjectRepository(PlanProjectRepository planProjectRepository) {
        this.planProjectRepository = planProjectRepository;
    }

    @Autowired
    public void setChannelSyncRepository(ChannelSyncRepository channelSyncRepository) {
        this.channelSyncRepository = channelSyncRepository;
    }

    //</editor-fold>

    /**
     * @param args     userLoginName, planName, assignedBy, planType, projectCategoryName, planDate, projectName, storeName, planStatus
     * @param pageable pageable
     * @return Page&lt;PlanJob&gt;
     */
    public Page<PlanJob> findMyPlans(Map<String, String> args, Pageable pageable) {
        String userLoginName = args.get("userLoginName");
        String planName = args.get("planName");
        String assignedBy = args.get("assignedBy");
        String planType = args.get("planType");
        String projectCategoryName = args.get("projectCategoryName");
        Object planDateObject = args.get("planDate");
        Date planDate = null;
        if (planDateObject != null) {
            DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
            try {
                planDate = dateFormat.parse(planDateObject.toString());
            } catch (ParseException e) {
            }
        }
        String projectName = args.get("projectName");
        String storeName = args.get("storeName");
        String planStatus = args.get("planStatus");

        List<Object> parameterValues = new ArrayList<Object>();
        StringBuilder from = new StringBuilder();
        from.append("\nFROM dm_plan_job o, dm_plan_project planProject, dm_plan plan, dm_project project, dm_project_category projectCategory, md_pos pos");

        StringBuilder where = new StringBuilder();
        where.append("\nWHERE o.plan_project=planProject.id AND planProject.plan=plan.id AND planProject.project_code=project.code");
        where.append("\nAND projectCategory.id=project.category AND pos.pos_code=o.store_code");
        and("plan.user_login_name", userLoginName, where, parameterValues);
        andLike("plan.name", planName, where, parameterValues);
        and("plan.assigned_by", assignedBy, where, parameterValues);
        and("plan.type", planType, where, parameterValues);
        and("projectCategory.name", projectCategoryName, where, parameterValues);
        and("plan.plan_date", planDate, where, parameterValues);
        andLike("project.name", projectName, where, parameterValues);
        andLike("pos.pos_name", storeName, where, parameterValues);
        and("plan.status", planStatus, where, parameterValues);

        StringBuilder order = new StringBuilder();
        if (pageable.getSort() != null) {
            Iterator<Sort.Order> iterator = pageable.getSort().iterator();
            boolean useComma = false;
            while (iterator.hasNext()) {
                Sort.Order orderItem = iterator.next();
                order.append(useComma ? ',' : "\nORDER BY ").append(orderItem.getProperty()).append(' ').append(orderItem.getDirection());
                useComma = true;
            }
        }

        StringBuilder countSql = new StringBuilder();
        countSql.append("SELECT count(*)").append(from).append(where);
        Query countQuery = entityManager.createNativeQuery(countSql.toString());
        for (int i = 0; i < parameterValues.size(); i++) {
            countQuery.setParameter(i + 1, parameterValues.get(i));
        }
        long count = ((Number) countQuery.getSingleResult()).longValue();
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT id,version,priority,start_time,end_time,store_code,job,plan_project FROM (SELECT rownum ROWNUM_, row_.* FROM(SELECT o.*").append(from).append(where).append(order);
        sql.append(") row_ WHERE rownum<=?) WHERE ROWNUM_>?");
        parameterValues.add(pageable.getOffset() + pageable.getPageSize());
        parameterValues.add(pageable.getOffset());
        if (log.isDebugEnabled()) {
            log.debug(sql.toString());
        }
        Query query = entityManager.createNativeQuery(sql.toString());
        for (int i = 0; i < parameterValues.size(); i++) {
            query.setParameter(i + 1, parameterValues.get(i));
            if (log.isDebugEnabled()) {
                log.debug("query param: {}", parameterValues.get(i));
            }
        }
        @SuppressWarnings("unchecked")
        List<Object[]> dataList = query.getResultList();
        List<PlanJob> content = new ArrayList<PlanJob>(dataList.size());
        for (Object[] data : dataList) {
            PlanJob obj = new PlanJob();
            obj.setId(((Number) data[0]).longValue());
            obj.setVersion(((Number) data[1]).intValue());
            obj.setPriority(((Number) data[2]).intValue());
            obj.setStartTime((Date) data[3]);
            obj.setEndTime((Date) data[4]);
            obj.setStoreCode((String) data[5]);
            obj.setJob(Job.findById(((Number) data[6]).longValue()));
            obj.setPlanProject(planProjectRepository.findOne(((Number) data[7]).longValue()));
            content.add(obj);
        }
        Page page = new PageImpl(content, pageable, count);
        return page;
    }

    /**
     * @param args     userLoginName, jobName, jobUploaded(0-未办; 1-已办), jobStatus, storeName, projectName, projectCategoryName, planDate, startTime(HH:mm), endTime(HH:mm)
     * @param pageable pageable
     * @return Page&lt;PlanJobResult&gt;
     */
    public Page<PlanJobResult> findJobResults(Map<String, String> args, Pageable pageable) {
        String userLoginName = args.get("userLoginName");
        String userLoginNameList = args.get("userLoginNameList");
        String jobName = args.get("jobName");
        String jobStatus = args.get("jobStatus");
//        String finishStatus = args.get("finishStatus");
        String storeName = args.get("storeName");
        String projectName = args.get("projectName");
        String projectCategoryName = args.get("projectCategoryName");
        String planDateString = args.get("planDate");
        String projectCodeList = args.get("projectCodeList");
        Date planDate = null;
        if (planDateString != null && !planDateString.isEmpty()) {
            DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
            try {
                planDate = dateFormat.parse(planDateString);
            } catch (ParseException e) {
            }
        }
        String startTimeString = args.get("startTime");
        Date startTime = null;
        if (startTimeString != null && !startTimeString.isEmpty()) {
            DateFormat dateFormat = new SimpleDateFormat("HH:mm");
            try {
                startTime = dateFormat.parse(startTimeString);
            } catch (Exception ignore) {
            }
        }
        String endTimeString = args.get("endTime");
        Date endTime = null;
        if (endTimeString != null && !endTimeString.isEmpty()) {
            DateFormat dateFormat = new SimpleDateFormat("HH:mm");
            try {
                endTime = dateFormat.parse(endTimeString);
            } catch (Exception ignore) {
            }
        }

        List<Object> parameterValues = new ArrayList<Object>();
        StringBuilder from = new StringBuilder();
        from.append("\nFROM dm_wf_plan_job_result o, dm_plan_job planJob, dm_wf_job job," +
                "dm_plan_project planProject, dm_plan plan, dm_project project, dm_project_category projectCategory, md_pos pos");

        StringBuilder where = new StringBuilder();
        where.append("\nWHERE o.plan_job=planJob.id AND planJob.job=job.id AND planJob.plan_project=planProject.id");
        where.append("\nAND planProject.plan=plan.id AND planProject.project_code=project.code");
        where.append("\nAND projectCategory.id=project.category AND pos.pos_code=planJob.store_code");
        and("plan.user_login_name", userLoginName, where, parameterValues);
        andIn("plan.user_login_name", userLoginNameList, where, parameterValues);
        andIn("project.code", projectCodeList, where, parameterValues);
        andLike("job.name", jobName, where, parameterValues);
        if (PlanJobResult.JOB_STATUS_NOT_FINISHED.equals(jobStatus)) {
            where.append("\nAND o.finished_at is null");
        } else if (PlanJobResult.JOB_STATUS_DELAYED.equals(jobStatus)) {
            where.append("\nAND o.finished_at is not null AND o.job_status='").append(PlanJobResult.JOB_STATUS_NOT_FINISHED).append("'");
        } else if (PlanJobResult.JOB_STATUS_FINISHED.equals(jobStatus)) {
            where.append("\nAND o.job_status='").append(PlanJobResult.JOB_STATUS_FINISHED).append("'");
        }
        andLike("pos.pos_name", storeName, where, parameterValues);
        andLike("project.name", projectName, where, parameterValues);
        and("projectCategory.name", projectCategoryName, where, parameterValues);
        and("plan.plan_date", planDate, where, parameterValues);
        if (notEmpty(startTime)) {
            where.append("\nAND planJob.start_time>=?");
            parameterValues.add(startTime);
        }
        if (notEmpty(endTime)) {
            where.append("\nAND planJob.end_time<=?");
            parameterValues.add(endTime);
        }
        StringBuilder order = new StringBuilder();
        if (pageable.getSort() != null) {
            Iterator<Sort.Order> iterator = pageable.getSort().iterator();
            boolean useComma = false;
            while (iterator.hasNext()) {
                Sort.Order orderItem = iterator.next();
                order.append(useComma ? ',' : "\nORDER BY ").append(orderItem.getProperty()).append(' ').append(orderItem.getDirection());
                useComma = true;
            }
        }

        StringBuilder countSql = new StringBuilder();
        countSql.append("SELECT count(*)").append(from).append(where);
        Query countQuery = entityManager.createNativeQuery(countSql.toString());
        for (int i = 0; i < parameterValues.size(); i++) {
            countQuery.setParameter(i + 1, parameterValues.get(i));
        }
        long count = ((Number) countQuery.getSingleResult()).longValue();
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT id,version,plan_job,job_status,notes,finished_at FROM (SELECT rownum ROWNUM_, row_.* FROM(SELECT o.*").append(from).append(where).append(order);
        sql.append(") row_ WHERE rownum<=?) WHERE ROWNUM_>?");
        parameterValues.add(pageable.getOffset() + pageable.getPageSize());
        parameterValues.add(pageable.getOffset());
        if (log.isDebugEnabled()) {
            log.debug(sql.toString());
        }
        Query query = entityManager.createNativeQuery(sql.toString());
        for (int i = 0; i < parameterValues.size(); i++) {
            query.setParameter(i + 1, parameterValues.get(i));
            if (log.isDebugEnabled()) {
                log.debug("query param: {}", parameterValues.get(i));
            }
        }
        @SuppressWarnings("unchecked")
        List<Object[]> dataList = query.getResultList();
        List<PlanJobResult> content = new ArrayList<PlanJobResult>(dataList.size());
        for (Object[] data : dataList) {
            PlanJobResult obj = new PlanJobResult();
            obj.setId(((Number) data[0]).longValue());
            obj.setVersion(((Number) data[1]).intValue());
            obj.setPlanJob(PlanJob.findById(((Number) data[2]).longValue()));
            obj.setJobStatus((String) data[3]);
            obj.setNotes((String) data[4]);
            obj.setFinishedAt((Date) data[5]);
            content.add(obj);
        }
        Page page = new PageImpl(content, pageable, count);
        return page;
    }

    public Page<ChannelSyncDto> findChannelSync(Map<String, String> args, Pageable pageable) {
        String adName = args.get("adName");
        String name = args.get("name");
        String sysCategory = args.get("sysCategory");
        String category = args.get("category");

        List<Object> sqlParams = new ArrayList<Object>();

        StringBuilder from = new StringBuilder();
        from.append("\n from dm_channel_sync c");
        from.append("\n left join md_pos_kind k on k.pos_kind_code=c.channel_cat_code");
        from.append("\n left join md_pos_sys_type s on s.pos_sys_type_code=c.channel_sys_cat");

        StringBuilder where = new StringBuilder("\n where type=0 and 1=1");
        if (adName != null && !adName.trim().isEmpty()) {
            where.append("\n and (c.channel_city_ad_name like ? or channel_province_ad_name like ? or channel_ad_name like ?)");
            String param = "%" + adName.trim() + "%";
            sqlParams.add(param);
            sqlParams.add(param);
            sqlParams.add(param);
        }
        andLike("c.channel_name", name, where, sqlParams);
        andLike("s.pos_sys_type_name", sysCategory, where, sqlParams);
        andLike("k.pos_kind_name", category, where, sqlParams);

        StringBuilder countSql = new StringBuilder();
        countSql.append("select count(*)").append(from).append(where);
        Query query = entityManager.createNativeQuery(countSql.toString());
        for (int i = 0; i < sqlParams.size(); i++) {
            query.setParameter(i + 1, sqlParams.get(i));
        }
        long count = ((Number) query.getSingleResult()).longValue();

        StringBuilder dataSql = new StringBuilder();
        dataSql.append("select * from (select rownum ROWNUM_, row_.* from(" +
                "select c.id, c.channel_code, c.channel_name, c.channel_province_ad_name||c.channel_city_ad_name||c.channel_ad_name ad_name," +
                "s.pos_sys_type_name sys_category, k.pos_kind_name category,c.channel_city_ad_name,c.channel_address ")
                .append(from).append(where);
        dataSql.append(") row_ where rownum<=?) where ROWNUM_>?");
        sqlParams.add(pageable.getOffset() + pageable.getPageSize());
        sqlParams.add(pageable.getOffset());
        query = entityManager.createNativeQuery(dataSql.toString());
        for (int i = 0; i < sqlParams.size(); i++) {
            query.setParameter(i + 1, sqlParams.get(i));
        }
        List<Object[]> dataList = query.getResultList();
        List<ChannelSyncDto> retList = new ArrayList<ChannelSyncDto>(dataList.size());
        for (Object[] data : dataList) {
            ChannelSyncDto dto = new ChannelSyncDto();
            retList.add(dto);
            dto.setId(((Number) data[1]).longValue());
            dto.setCode((String) data[2]);
            dto.setName((String) data[3]);
            dto.setAdName((String) data[4]);
            dto.setSysCategory((String) data[5]);
            dto.setCategory((String) data[6]);
            dto.setCityAdName((String) data[7]);
            dto.setAddress((String) data[8]);
        }
        return new PageImpl<ChannelSyncDto>(retList, pageable, count);
    }

    private boolean notEmpty(Object value) {
        if (value == null) return false;
        if (value instanceof String && ((String) value).isEmpty()) return false;
        return true;
    }

    private void and(String key, Object value, StringBuilder whereClause, List<Object> parameterStack) {
        if (notEmpty(value)) {
            whereClause.append("\nAND ").append(key).append("=?");
            parameterStack.add(value);
        }
    }

    private void andLike(String key, Object value, StringBuilder whereClause, List<Object> parameterStack) {
        if (notEmpty(value)) {
            whereClause.append("\nAND ").append(key).append(" like ?");
            parameterStack.add("%" + String.valueOf(value) + "%");
        }
    }

    private void andIn(String key, String value, StringBuilder whereClause, List<Object> parameterStack) {
        if (notEmpty(value)) {
            whereClause.append("\nAND ").append(key).append(" in (").append(value).append(")");
        }
    }

    public Page<Map> findProjectStore(Map<String, String> param, Pageable pageable) {

        String name = param.get("storeName");
        String code = param.get("storeCode");
        String sysCategory = param.get("sysCategory");
        String category = param.get("category");

        String projectId = param.get("projectId");
        String adName = param.get("adName");



        String paramSql = " t.id," +
                " t.channel_code, " +
                " t.project, " +
                " t.required_sales_number, " +
                " t2.channel_name, " +
                " t2.channel_address, " +
                " t2.channel_province_ad_name, " +
                " t2.channel_city_ad_name, " +
                " t2.channel_ad_name, " +
                " count(san.projsales_id) assigned_sales_number ";

        StringBuilder groupSql = new StringBuilder("\n group by t.id, t.channel_code, t.project, t.required_sales_number")
                .append(", t2.channel_name, t2.channel_address, t2.channel_province_ad_name, t2.channel_city_ad_name, t2.channel_ad_name");

        StringBuffer selectSql = new StringBuffer("select ").append(paramSql)
                .append("\n from dm_project_store t")
                .append("\n join DM_CHANNEL_SYNC t2 on t.channel_code = t2.channel_code")
                .append("\n left join DM_PROJECT_SALESPROMOTER_STORE san on san.PROJSTORE_ID=t.id");

        StringBuffer countSql = new StringBuffer("select count(0) from (")
                .append(selectSql);
        // from dm_project_store t, DM_CHANNEL_SYNC t2")
//                .append("\n from dm_project_store t")
//                .append("\n join DM_CHANNEL_SYNC t2 on t.channel_code = t2.channel_code")
//                .append("\n left join DM_PROJECT_SALESPROMOTER_STORE san on san.PROJSTORE_ID=t.id");

        StringBuffer whereSql = new StringBuffer(" where 1 = 1");

        if (StringUtils.hasText(projectId)) {
            whereSql.append(" and t.project = " + projectId);
        }

        if (StringUtils.hasText(code)) {
        	try {
        		whereSql.append(" and t2.channel_code=" + Long.valueOf(code));
			} catch (Exception e) {
				//code 必须是数字
				whereSql.append(" and t2.channel_code= -999");
			}
        }

        if (StringUtils.hasText(sysCategory)) {
            whereSql.append(" and t2.channel_sys_cat=" + sysCategory);
        }

        if (StringUtils.hasText(category)) {
            whereSql.append(" and t2.channel_cat_code=" + category);
        }

        if (StringUtils.hasText(adName)) {
            whereSql.append(" and (t2.channel_city_ad_name like '%" + adName + "%' or");
            whereSql.append(" t2.channel_ad_name like '%" + adName + "%' or");
            whereSql.append(" t2.channel_province_ad_name like '%" + adName + "%')");
        }

        if (StringUtils.hasText(name)) {
            whereSql.append(" and t2.channel_name like '%" + name + "%'");
        }

        countSql.append(whereSql).append(groupSql).append(")");
        selectSql.append(whereSql).append(groupSql);

        String pageSql = new Oracle10gDialect().getLimitString(selectSql.toString(), pageable.getOffset(), pageable.getPageSize());

        int count = jdbcTemplate.queryForInt(countSql.toString());
        List data = null;
        if (pageable.getOffset() > 0) {
            data = jdbcTemplate.queryForList(pageSql, new Object[]{pageable.getOffset() + pageable.getPageSize(), pageable.getOffset()});
        } else {
            data = jdbcTemplate.queryForList(pageSql, new Object[]{pageable.getPageSize()});
        }

        return new PageImpl(data, pageable, count);
    }

}
